import sys
import re
from pathlib import Path
from collections import Counter

DEFAULT_EXCEL_PATH = Path('d:/Desktop/DYVA/V2/研究样本表.xlsx')
DEFAULT_COL_INDEX = 6  # F列（从1开始）

LABELS = ["≤15秒", "≤1分钟", "≤5分钟", "＞5分钟"]


def normalize_text(s: str) -> str:
    s = s.strip()
    # 全角转半角常见符号
    s = s.replace('：', ':').replace('．', '.').replace('，', ',')
    # 中文单位替换为 h/m/s
    s = s.replace('小时', 'h').replace('时长', '')
    s = s.replace('时', 'h').replace('分钟', 'm').replace('分', 'm').replace('秒', 's')
    # 常见英文单位规范小写
    s = s.lower()
    return s


def parse_duration_to_seconds(val) -> int | None:
    if val is None:
        return None
    if isinstance(val, (int, float)):
        try:
            return int(round(float(val)))
        except Exception:
            return None
    if not isinstance(val, str):
        try:
            val = str(val)
        except Exception:
            return None
    s = normalize_text(val)
    if not s:
        return None

    # 纯数字/浮点：直接视为秒
    m = re.fullmatch(r"(\d+)(?:\.(\d+))?", s)
    if m:
        return int(round(float(s)))

    # h:m:s 或 m:s
    m = re.fullmatch(r"(\d+):(\d+):(\d+)", s)
    if m:
        h, mi, se = map(int, m.groups())
        return h * 3600 + mi * 60 + se
    m = re.fullmatch(r"(\d+):(\d+)(?:\.(\d+))?", s)
    if m:
        mi, se = map(int, m.group(1, 2))
        return mi * 60 + se

    # 组合单位：xh ym zs（顺序不限）
    # 支持如：1h2m3s、2m40s、40s、2m、1h
    total = 0
    matched = False
    for unit, mult in (("h", 3600), ("m", 60), ("s", 1)):
        for mm in re.finditer(rf"(\d+)\s*{unit}", s):
            total += int(mm.group(1)) * mult
            matched = True
    if matched:
        return total if total > 0 else 0

    return None


def to_label(seconds: int) -> str:
    if seconds <= 15:
        return "≤15秒"
    if seconds <= 60:
        return "≤1分钟"
    if seconds <= 300:
        return "≤5分钟"
    return "＞5分钟"


def main():
    excel_path = Path(sys.argv[1]) if len(sys.argv) > 1 else DEFAULT_EXCEL_PATH
    col_index = int(sys.argv[2]) if len(sys.argv) > 2 else DEFAULT_COL_INDEX

    try:
        import openpyxl
    except ImportError:
        print("[错误] 需要 openpyxl 支持，请安装后重试 (建议在虚拟环境中安装)。")
        sys.exit(1)

    if not excel_path.exists():
        print(f"[错误] Excel文件不存在: {excel_path}")
        sys.exit(1)

    wb = openpyxl.load_workbook(excel_path, read_only=True, data_only=True)
    ws = wb.active

    label_counts = Counter()
    parsed = 0
    empty_cells = 0
    unparsable = 0
    issues = []  # (row_index, value, reason)

    # 假设首行是表头，从第二行开始
    for idx, row in enumerate(ws.iter_rows(values_only=True), start=1):
        val = row[col_index - 1] if len(row) >= col_index else None
        if idx == 1:
            # 表头行跳过
            continue
        if val is None or (isinstance(val, str) and not val.strip()):
            empty_cells += 1
            issues.append((idx, val, '空白'))
            continue
        sec = parse_duration_to_seconds(val)
        if sec is None:
            unparsable += 1
            issues.append((idx, val, '无法解析'))
            continue
        label_counts[to_label(sec)] += 1
        parsed += 1

    print(f"[Excel] 路径: {excel_path}")
    print(f"[统计] 解析成功: {parsed}，空白: {empty_cells}，无法解析: {unparsable}")
    print("[分布]")
    for label in LABELS:
        print(f"  {label} -> {label_counts.get(label, 0)}")

    # 与目标分布比对
    target = {"≤15秒": 4, "≤1分钟": 47, "≤5分钟": 224, "＞5分钟": 115}
    match = all(label_counts.get(k, 0) == v for k, v in target.items())
    print(f"[是否匹配目标分布] {'是' if match else '否'}")

    if issues:
        print("[问题单元格]")
        for idx, val, reason in issues:
            print(f"  行 {idx}，值: {val!r}，原因: {reason}")


if __name__ == '__main__':
    main()
